package com.kelai.repository;

import com.kelai.common.IRepository;
import com.kelai.domain.CustomerEntity;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;

/**
 * 客户持久化服务
 * Created by Silence on 2016/11/19.
 */
@Repository("customerRepository")
public interface ICustomerRepository extends IRepository<CustomerEntity> {

    @Query("select c from CustomerEntity c where c.parent is null ")
    List<CustomerEntity> findTopCustomer();

    /**
     * 根据客户ID查找渠道商ID
     * @return
     */
    @Query(value = "select ven_id from r_ven_cus where cus_id = :cusId" , nativeQuery = true)
    String findVendorByCusId(@Param("cusId") String cusId);



    /**
     * 更改关注状态 Y已关注 N未关注
     * @param followed
     * @param id
     * @return
     */
    @Modifying
    @Query(value = "update T_CUSTOMER set followed = :followed where pk_id = :id" , nativeQuery = true)
    int updateFollowStatus(@Param("followed") String followed, @Param("id") String id);

    /**
     * 根据店铺帐号查找旗下所有店铺
     * @param customerId
     * @return
     */
    @Query(value = " with cte as " +
            " ( " +
            " select a.* from t_customer a where pk_id in (select pk_id from t_customer cus , r_cus_acn rca " +
            " where cus.pk_id = rca.cus_id and rca.acn_id = :customerId )" +
            " union all " +
            " select k.*  from t_customer k inner join cte c on c.pk_id = k.parent_id " +
            " )" +
            " select pk_id,code,name,open_date,close_date,status from cte where category = 3 " , nativeQuery = true)
    List<Object[]> findShopsByShop(@Param("customerId") String customerId);
    /**
     * 根据客户帐号查找旗下所有店铺
     * @param customerId
     * @return
     */
    @Query(value = " with cte as " +
            " ( " +
            " select a.* from t_customer a where pk_id in (select pk_id from t_customer cus , r_cus_acn rca " +
            " where cus.pk_id = rca.cus_id and rca.acn_id = :customerId )" +
            " union all " +
            " select k.*  from t_customer k inner join cte c on c.pk_id = k.parent_id " +
            " )" +
            " select pk_id,code,name,open_date,close_date,status from cte where category = 3 and pk_id not in (select pk_id from t_customer cus , r_cus_acn rca " +
            " where cus.pk_id = rca.cus_id and rca.acn_id = :customerId )" , nativeQuery = true)
    List<Object[]> findShopsByCustomer(@Param("customerId") String customerId);
    /**
     * 根据客户帐号查找旗下所有店铺ID
     * @param customerId
     * @return
     */
    @Query(value = " with cte as " +
            " ( " +
            " select a.* from t_customer a where pk_id in (select pk_id from t_customer cus , r_cus_acn rca " +
            " where cus.pk_id = rca.cus_id and rca.acn_id = :customerId )" +
            " union all " +
            " select k.*  from t_customer k inner join cte c on c.pk_id = k.parent_id " +
            " )" +
            " select pk_id from cte where category = 3 and pk_id not in (select pk_id from t_customer cus , r_cus_acn rca " +
            " where cus.pk_id = rca.cus_id and rca.acn_id = :customerId )" , nativeQuery = true)
    List<String> findShopIdsByCustomer(@Param("customerId") String customerId);

    /**
     * 根据渠道商帐号查找旗下所有店铺
     * @param vendorId
     * @return
     */
    @Query(value = " with cte as " +
            " ( " +
            " select a.* from t_customer a where pk_id in ( " +
            " select cus.pk_id from t_customer cus , r_ven_cus rvc " +
            " where cus.pk_id = rvc.cus_id and rvc.ven_id in ( " +
            " select pk_id from t_vendor ven , r_ven_acn rva " +
            " where ven.pk_id = rva.ven_id and rva.acn_id = :vendorId " +
            " ) ) " +
            " union all " +
            " select k.*  from t_customer k inner join cte c on c.pk_id = k.parent_id and k.category = 3 " +
            " ) " +
            " select pk_id,code,name,open_date,close_date,status from cte where pk_id not in ( " +
            " select cus.pk_id from t_customer cus , r_ven_cus rvc " +
            " where cus.pk_id = rvc.cus_id and rvc.ven_id in ( " +
            " select pk_id from t_vendor ven , r_ven_acn rva " +
            " where ven.pk_id = rva.ven_id and rva.acn_id = :vendorId " +
            " ) ) " , nativeQuery = true)
    List<Object[]> findShopsByVendor(@Param("vendorId") String vendorId);
    /**
     * 根据渠道商帐号查找旗下所有店铺ID
     * @param vendorId
     * @return
     */
    @Query(value = " with cte as " +
            " ( " +
            " select a.* from t_customer a where pk_id in ( " +
            " select cus.pk_id from t_customer cus , r_ven_cus rvc " +
            " where cus.pk_id = rvc.cus_id and rvc.ven_id in ( " +
            " select pk_id from t_vendor ven , r_ven_acn rva " +
            " where ven.pk_id = rva.ven_id and rva.acn_id = :vendorId " +
            " ) ) " +
            " union all " +
            " select k.*  from t_customer k inner join cte c on c.pk_id = k.parent_id and k.category = 3 " +
            " ) " +
            " select pk_id from cte where pk_id not in ( " +
            " select cus.pk_id from t_customer cus , r_ven_cus rvc " +
            " where cus.pk_id = rvc.cus_id and rvc.ven_id in ( " +
            " select pk_id from t_vendor ven , r_ven_acn rva " +
            " where ven.pk_id = rva.ven_id and rva.acn_id = :vendorId " +
            " ) ) " , nativeQuery = true)
    List<String> findShopIdsByVendor(@Param("vendorId") String vendorId);
    /**
     * 根据管理员帐号查找旗下所有店铺
     * @return
     */
    @Query(value = "select pk_id,code,name,open_date,close_date,status from t_customer where category = 3" , nativeQuery = true)
    List<Object[]> findShopsByAdmin();
    /**
     * 根据管理员帐号查找旗下所有店铺ID
     * @return
     */
    @Query(value = "select pk_id from t_customer where category = 3" , nativeQuery = true)
    List<String> findShopIdsByAdmin();

    /**
     * 根据店铺帐号查找旗下所有客户和目录 Csags : customers and groups
     * @param shopAcctId
     * @return
     */
    @Query(value = "with cte as" +
            " (" +
            " select a.* from t_customer a where pk_id in (select pk_id from t_customer cus , r_cus_acn rca " +
            " where cus.pk_id = rca.cus_id and rca.acn_id = :shopAcctId )" +
            " union all" +
            " select k.*  from t_customer k inner join cte c on k.pk_id = c.parent_id and k.category in (1,2)" +
            " )" +
            " select pk_id,code,name,category from cte" , nativeQuery = true)
    List<Object[]> findCsagsByShop(@Param("shopAcctId") String shopAcctId);
    /**
     * 根据客户帐号查找旗下所有客户和目录 Csags : customers and groups
     * @param customerAcctId
     * @return
     */
    @Query(value = "with cte as" +
            " (" +
            " select a.* from t_customer a where pk_id in (select pk_id from t_customer cus , r_cus_acn rca " +
            " where cus.pk_id = rca.cus_id and rca.acn_id = :customerAcctId )" +
            " union all" +
            " select k.*  from t_customer k inner join cte c on c.pk_id = k.parent_id and k.category in (1,2)" +
            " )" +
            " select pk_id,code,name,category from cte" , nativeQuery = true)
    List<Object[]> findCsagsByCustomer(@Param("customerAcctId") String customerAcctId);
    /**
     * 根据渠道商帐号查找旗下所有客户和目录 Csags : customers and groups
     * @param vendorAcctId
     * @return
     */
    @Query(value = "with cte as" +
            " (" +
            " select a.* from t_customer a where pk_id in (" +
            " select cus.pk_id from t_customer cus , r_ven_cus rvc " +
            " where cus.pk_id = rvc.cus_id and rvc.ven_id in (" +
            " select pk_id from t_vendor ven , r_ven_acn rva " +
            " where ven.pk_id = rva.ven_id and rva.acn_id = :vendorAcctId" +
            " ) " +
            " ) " +
            " union all " +
            " select k.*  from t_customer k inner join cte c on c.pk_id = k.parent_id and k.category in (1,2)" +
            " )" +
            " select pk_id,code,name,category from cte" , nativeQuery = true)
    List<Object[]> findCsagsByVendor(@Param("vendorAcctId") String vendorAcctId);
    /**
     * 根据管理员帐号查找旗下所有客户和目录 Csags : customers and groups
     * @return
     */
    @Query(value = "select pk_id,code,name from t_customer where category in (1,2)" , nativeQuery = true)
    List<Object[]> findCsagsByAdmin();



    /**
     * 根据客户帐号查找旗下所有客户
     * @param customerAcctId
     * @return
     */
    @Query(value = "with cte as" +
            " (" +
            " select a.* from t_customer a where pk_id in (select pk_id from t_customer cus , r_cus_acn rca " +
            " where cus.pk_id = rca.cus_id and rca.acn_id = :customerAcctId )" +
            " union all" +
            " select k.*  from t_customer k inner join cte c on c.pk_id = k.parent_id and k.category = 1" +
            " )" +
            " select pk_id,code,name from cte" , nativeQuery = true)
    List<Object[]> findCustomersByCustomer(@Param("customerAcctId") String customerAcctId);
    /**
     * 根据渠道商帐号查找旗下所有客户
     * @param vendorAcctId
     * @return
     */
    @Query(value = "with cte as" +
            " (" +
            " select a.* from t_customer a where pk_id in (" +
            " select cus.pk_id from t_customer cus , r_ven_cus rvc " +
            " where cus.pk_id = rvc.cus_id and rvc.ven_id in (" +
            " select pk_id from t_vendor ven , r_ven_acn rva " +
            " where ven.pk_id = rva.ven_id and rva.acn_id = :vendorAcctId" +
            " ) " +
            " ) " +
            " union all " +
            " select k.*  from t_customer k inner join cte c on c.pk_id = k.parent_id and k.category = 1" +
            " )" +
            " select pk_id,code,name from cte" , nativeQuery = true)
    List<Object[]> findCustomersByVendor(@Param("vendorAcctId") String vendorAcctId);

    /**
     * 根据渠道商ID查找旗下所有客户
     * @param vendorId
     * @return
     */
    @Query(value = " with cte as " +
            " ( " +
            "  select a.* from t_customer a where pk_id in ( " +
            "  select cus.pk_id from t_customer cus , r_ven_cus rvc " +
            "  where cus.pk_id = rvc.cus_id and rvc.ven_id = :vendorId " +
            "  ) " +
            "  union all " +
            "  select k.*  from t_customer k inner join cte c on c.pk_id = k.parent_id and k.category = 1 " +
            "  ) " +
            "  select pk_id,code,name from cte " , nativeQuery = true)
    List<Object[]> findCustomersByVendorId(@Param("vendorId") String vendorId);


    /**
     * 根据管理员帐号查找旗下所有客户和目录 Csags : customers and groups
     * @return
     */
    @Query(value = "select pk_id,code,name from t_customer where category = 1" , nativeQuery = true)
    List<Object[]> findCustomersByAdmin();



    /**
     * 根据客户或者目录ID查找旗下所有店铺
     * @param customerId
     * @return
     */
    @Query(value = " with cte as " +
            "( " +
            "select a.* from t_customer a where pk_id = :customerId " +
            "union all " +
            "select k.*  from t_customer k inner join cte c on c.pk_id = k.parent_id " +
            ")" +
            " select pk_id,code,name,shop_type,contact_person,telephone,mobile,address,guarantee_dead_line,open_date,close_date" +
            " from cte where pk_id != :customerId and category = 3 and status=1 " , nativeQuery = true)
    List<Object[]> findShopsByCusId(@Param("customerId") String customerId);

    /**
     * 根据客户或者目录ID查找旗下所有被关注的店铺
     * @param customerId
     * @return
     */
    @Query(value = " with cte as " +
            "( " +
            "select a.* from t_customer a where pk_id = :customerId " +
            "union all " +
            "select k.*  from t_customer k inner join cte c on c.pk_id = k.parent_id " +
            ")" +
            " select pk_id,code,name,shop_type,contact_person,telephone,mobile,address,guarantee_dead_line,open_date,close_date"+
            " from cte where pk_id != :customerId and category = 3 and followed='Y' and status=1 " , nativeQuery = true)
    List<Object[]> findFollowedShopsByCusId(@Param("customerId") String customerId);


    /**
     * 根据客户ID查找旗下所有客户和目录
     * @param customerId
     * @return
     */
    @Query(value = " with cte as " +
            " ( " +
            " select a.* from t_customer a where pk_id = :customerId " +
            " union all " +
            " select k.*  from t_customer k inner join cte c on c.pk_id = k.parent_id " +
            " )" +
            " select pk_id,code,name from cte where category in (1,2) " , nativeQuery = true)
    List<Object[]> findShopParentByCusId(@Param("customerId") String customerId);




}
